<?php

/**
 * 注册
 *
 * @param string $username
 * @param string $password
 * @param int $rank
 */
function sql_signin( $username, $password, $rank = 0 ) {
	$createtime  = time();
	$addusername = addslashes( $username );
	global $conn;
	// mysqli_query($conn,"INSERT INTO bbs0_users(user_username,user_password,user_createtime,user_rank)VALUE('$username','$password',$createtime,$rank)");
	$md5password = md5( trim( $password ) );
	$result      = mysqli_query( $conn, "INSERT INTO bbs0_users(user_username,user_password,user_createtime,user_rank)VALUES('$addusername','$md5password',$createtime,$rank)" );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail' );
	}
}

/**
 * 登录
 *
 * @param string $username
 * @param string $password
 * @param string $verify
 */
function sql_login( $username, $password, $verify ) {
	if ( $verify != $_SESSION['verify'] ) {
		exit( '验证码错误' );
	} else {
		global $conn;
		$addusername = addslashes( $username );
		$md5password = md5( trim( $password ) );
		$result      = mysqli_query( $conn, "SELECT user_uid, user_rank, user_theme, user_logintime FROM bbs0_users WHERE user_username='$addusername' AND user_password='$md5password'" );
		$array       = mysqli_fetch_array( $result );
		if ( $array ) {
			if ( $array['user_logintime'] > time() ) {
				echo "由于您的操作异常已被管理员踢下线，请于" . floor( ( date( $array['user_logintime'] ) - time() ) / 60 ) . "分钟后再进行登录操作";
				exit();
			}
			$_SESSION['uid']   = $array['user_uid'];
			$_SESSION['color'] = $array['user_theme'];
			$uid               = $array['user_uid'];
			$_SESSION['rank']  = $array['user_rank'];
			$time              = time();
			mysqli_query( $conn, "UPDATE bbs0_users SET user_lastlogin=$time, user_login = 1 WHERE user_uid='$uid'" );
			exit( 'success' );
		} else {
			exit( 'fail' );
		}
	}
}

function sql_loginout( $uid, $time ) {
	global $conn;
	$time   = time() + $time;
	$result = mysqli_query( $conn, "UPDATE bbs0_users SET user_login = 0, user_logintime = $time WHERE user_uid = '$uid'" );
	if ( $result ) {
		exit( "success" );
	} else {
		exit( mysqli_error( $conn ) );
	}
}

/**
 * 判断是否被踢下线
 *
 * @param $uid
 *
 * @return array
 */
function sql_uid_login( $uid ) {
	global $conn;
	$result       = array();
	$result_array = mysqli_fetch_array( mysqli_query( $conn, "SELECT user_login FROM bbs0_users WHERE user_uid = '$uid'" ) );
	if ( $result_array['user_login'] == 0 ) {
		$result[0] = true;
		$assoc     = mysqli_fetch_assoc( mysqli_query( $conn, "SELECT user_logintime FROM bbs0_users WHERE user_uid = '$uid'" ) );
		$result[1] = date( "Y-m-d H:i:s", $assoc['user_logintime'] );

		return $result;
	} else {
		$result[0] = false;

		return $result;
	}
}

/**
 * 已知uid，查出用户名
 *
 * @param int $uid
 *
 * @return string
 */
function sql_searchName_login_uid_name( $uid ) {
	global $conn;
	$setSearchName = mysqli_fetch_array( mysqli_query( $conn, "SELECT user_username FROM bbs0_users WHERE user_uid = '$uid'" ) );

	return $setSearchName['user_username'];
}

/**
 * 已知UID获取头像
 *
 * @param int $uid
 *
 * @return string
 */
function sql_searchHimg_uid_himg( $uid ) {
	global $conn;
	$setSearchImg = mysqli_fetch_array( mysqli_query( $conn, "SELECT user_himg FROM bbs0_users WHERE user_uid = '$uid'" ) );

	return $setSearchImg['user_himg'];
}

/**
 * 已知uid获取所有数组
 *
 * @param int $uid
 *
 * @return array
 */
function sql_search_uid_all( $uid ) {
	global $conn;
	$searchRsult = mysqli_fetch_array( mysqli_query( $conn, "SELECT * FROM bbs0_users WHERE user_uid = '$uid'" ) );

	return $searchRsult;
}

/**
 * 已知等级值查询 等级名
 *
 * @param int $rank
 *
 * @return string
 */
function sql_search_rank_rankname( $rank ) {
	global $conn;
	switch ($rank){
		case "0":
			$rankName = "订阅者";
			break;
		case "10":
			$rankName = "管理员";
			break;
		default:
			$forumtitle = mysqli_fetch_assoc(mysqli_query($conn, "SELECT forum_name FROM bbs0_forums WHERE auth_edit = $rank"));
			$forumtitle = $forumtitle['forum_name'];
			$rankName = $forumtitle .= " 版主";
			break;
	}

	return $rankName;
}

/**
 * 正向输出论坛区
 *
 * @param int $num
 * @param int $pagesize
 *
 * @return mysqli_query
 */
function search_bbs_area_asc( $num = 0, $pagesize = 10 ) {
	global $conn;
	$search_area = mysqli_query( $conn, "SELECT * FROM bbs0_areas ORDER BY area_order ASC LIMIT $num , $pagesize" );

	return $search_area;
}

//
function sql_count_hidden_forumid_topics( $forumid ) {
	global $conn;
	$sql    = "SELECT COUNT(1) FROM bbs0_topics WHERE topic_forumid = '$forumid' AND topic_status = 0";
	$result = mysqli_fetch_array( mysqli_query( $conn, $sql ) );

	return $result['COUNT(1)'];
}

function sql_count_waitten_forumid_topics( $forumid ) {
	global $conn;
	$sql    = "SELECT COUNT(1) FROM bbs0_topics WHERE topic_forumid = '$forumid' AND topic_status = 2";
	$result = mysqli_fetch_array( mysqli_query( $conn, $sql ) );

	return $result['COUNT(1)'];
}

/**
 * 逆向输出论坛区
 *
 * @param int $num
 * @param int $pagesize
 *
 * @return mysqli_query
 */
function search_bbs_area_desc() {
	global $conn;
	$search_area = mysqli_query( $conn, "SELECT * FROM bbs0_areas ORDER BY area_order DESC" );

	return $search_area;
}

/**
 * 增加论坛区
 *
 * @param string $title
 * @param int $order
 */
function sql_addArea( $title, $order ) {
	global $conn;
	$settitle    = addslashes( $title );
	$search_area = mysqli_query( $conn, "SELECT * FROM bbs0_areas ORDER BY area_id DESC" );
	while ( ( $orderNum = mysqli_fetch_array( $search_area ) ) != false ) {
		if ( $orderNum['area_order'] >= $order ) {
			$orderNumSet = $orderNum['area_order'];
			mysqli_query( $conn, "UPDATE bbs0_areas SET area_order=area_order+1 WHERE area_order=$orderNumSet" );
		}
	}
	$result = mysqli_query( $conn, "INSERT INTO bbs0_areas(area_title,area_order)VALUES('$settitle',$order)" );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail' );
	}
}

/**
 * 根据id删除论坛区
 *
 * @param int $areaid
 */
function sql_delArea( $areaid ) {
	global $conn;
	$result = mysqli_query( $conn, "DELETE FROM bbs0_areas WHERE area_id='$areaid'" );
	if ( $result ) {
		echo '<script>alert("删除成功");</script>';
		echo "<script>window.location='admin.php';</script>";
		// exit('success');
	} else {
		exit( 'fail' );
	}
}

/**
 * 根据论坛区id查出论坛区所有信息
 *
 * @param int $areaid
 *
 * @return array
 */
function sql_area( $areaid ) {
	global $conn;
	$result_array = mysqli_fetch_array( mysqli_query( $conn, "SELECT * from bbs0_areas WHERE area_id='$areaid'" ) );

	return $result_array;
}

/**
 * 修改论坛区名称
 */
function sql_deit_areatitle( $areaname, $areaid ) {
	global $conn;
	$result = mysqli_query( $conn, "UPDATE bbs0_areas SET area_title='$areaname' WHERE area_id='$areaid'" );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail' );
	}
}

// ---------论坛区操作
// -----------------------------------
// ---------板块操作
/**
 * 正向输出板块
 *
 * @param int $num
 * @param int $pagesize
 *
 * @return $search_forums
 */
function search_bbs_forums_asc( $area_id, $num, $pagesize ) {
	global $conn;
	$search_forums = mysqli_query( $conn, "SELECT * FROM bbs0_forums WHERE forum_area_id='$area_id' ORDER BY forum_id ASC LIMIT $num , $pagesize" );

	return $search_forums;
}

/**
 * 逆向输出板块
 *
 * @param int $num
 * @param int $pagesize
 *
 * @return mysqli_query
 */
function search_bbs_forums_desc() {
	global $conn;
	$search_area = mysqli_query( $conn, "SELECT * FROM bbs0_forums ORDER BY forum_id DESC" );

	return $search_area;
}

/**
 * 增加板块
 *
 * @param string $name
 * @param int $area_id
 */
function sql_addforums( $name, $area_id, $forum_desc ) {
	global $conn;
	$addname = addslashes( $name );
	$result  = mysqli_query( $conn, "INSERT INTO bbs0_forums(forum_name,forum_area_id,form_status,form_topics,forum_desc, form_last_post_id,auth_view,auth_read,auth_post,auth_reply,auth_edit,auth_delet,auth_vote,auth_pollcreate,auth_up,auth_file)VALUES('$addname','$area_id',1,0,'$forum_desc',1,1,0,1,1,1,1,1,1,1,1)" );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail ' . mysqli_error( $conn ) );
	}
}

/**
 * 根据id删除板块及所有主题
 *
 * @param int $area_id
 */
function sql_delForum( $area_id, $forum_id ) {
	global $conn;
	$result1 = mysqli_query( $conn, "DELETE FROM bbs0_topics WHERE topic_forumid='$forum_id'" );
	$result  = mysqli_query( $conn, "DELETE FROM bbs0_forums WHERE forum_id='$forum_id'" );
	if ( $result && $result1 ) {
		echo '<script>alert("删除成功");window.location.href="admin.php?type=area&id=' . $area_id . '"</script>';
		//header("Location:admin.php?type=area&id=$forum_id");
	} else {
		exit( 'fail' );
	}
}

/**
 * 根据论坛区id查出所有有关版面的信息
 *
 * @param int $area_id
 *
 * @return array
 */
function sql_forums_areaid_forum( $area_id ) {
	global $conn;
	$result_array = mysqli_fetch_array( mysqli_query( $conn, "SELECT * FROM bbs0_forums WHERE forum_area_id='$area_id'" ) );

	return $result_array;
}

/**
 * 根据论坛区id查出所有有关版面的信息
 *
 * @param int $area_id
 *
 * @return array
 */
function sql_areaid_forum( $area_id ) {
	global $conn;
	$result = mysqli_query( $conn, "SELECT * FROM bbs0_forums WHERE forum_area_id='$area_id' ORDER BY forum_id DESC " );

	return $result;
}

/**
 * 根据areaid求areas
 *
 * @param $areaid
 *
 * @return array|null|void
 */
function sql_areaid_areas( $areaid ) {
	global $conn;
	$sql    = "SELECT * FROM bbs0_areas WHERE area_id='$areaid'";
	$result = mysqli_fetch_array( mysqli_query( $conn, $sql ) );

	return $result;
}

//修改forum信息
function sql_edit_forum( $forumname, $forumid, $auth_post, $auth_edit, $forum_desc ) {
	global $conn;
	$result = mysqli_query( $conn, "UPDATE bbs0_forums SET forum_name='$forumname', auth_post = $auth_post, auth_edit=$auth_edit, forum_desc='$forum_desc' WHERE forum_id='$forumid'" );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail' );
	}
}

function sql_edit_auth_post( $auth_post, $forumid ) {
	global $conn;
	$result = mysqli_query( $conn, "UPDATE bbs0_forums SET auth_post='$auth_post' WHERE forum_id='$forumid'" );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail ' . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
	}
}

//-----------板块区结束
//-----------主题开始

/**
 * 已知板块id查板块信息
 *
 * @param int $forumid
 *
 * @return array
 */
function sql_forumid_forums( $forumid ) {
	global $conn;
	$result_array = mysqli_fetch_array( mysqli_query( $conn, "SELECT * FROM bbs0_forums WHERE forum_id='$forumid'" ) );

	return $result_array;
}

/**已知板块id求顺序列出所有主题信息
 *
 * @param int $forumid
 * @param int $first
 * @param int $last
 *
 * @return array|mysqli_result|void
 */
function sql_forumid_topics( $forumid, $filter, $first, $last ) {
	global $conn;
	switch ( $filter ) {
		case "0":
			goto B;
		case "1":
			$fil = 0;
			goto A;
		case "2":
			$fil = 1;
			goto A;
		case "3":
			$fil = 2;
			goto A;
		default:
			goto B;
	}
	A:
	$result_array = mysqli_query( $conn, "SELECT * FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = $fil ORDER BY topic_id DESC LIMIT $first, $last" );

	return $result_array;
	B:
	$result_array = mysqli_query( $conn, "SELECT * FROM bbs0_topics WHERE topic_forumid='$forumid' ORDER BY topic_id DESC LIMIT $first, $last" );

	return $result_array;
}

/**已知板块id求顺序列出正常主题信息
 *
 * @param int $forumid
 * @param int $first
 * @param int $last
 *
 * @return array|mysqli_result|void
 */
function sql_forumid_nomal_type_topics( $forumid, $type, $first, $last ) {
	global $conn;
	if ( $type != 0 ) {
		$result_array = mysqli_query( $conn, "SELECT * FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = 1 AND topic_type = $type AND topic_top = 0 ORDER BY topic_id DESC LIMIT $first, $last" );

		return $result_array;
	} else {
		$result_array = mysqli_query( $conn, "SELECT * FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = 1 AND topic_top = 0 ORDER BY topic_id DESC LIMIT $first, $last" );

		return $result_array;
	}
}

//已知板块求主题数
function sql_count_forumid_topic( $fid ) {
	global $conn;
	$result_array = mysqli_fetch_array( mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid = '$fid'" ) );

	return $result_array['COUNT(*)'];
}

function sql_forumid_nomal_type_topics_top( $forumid, $type, $first, $last ) {
	global $conn;
	if ( $type != 0 ) {
		$result_array = mysqli_query( $conn, "SELECT * FROM bbs0_topics WHERE topic_forumid='$forumid 'AND topic_status = 1 AND topic_type = $type AND topic_top = 1 ORDER BY topic_id DESC LIMIT $first, $last" );

		return $result_array;
	} else {
		$result_array = mysqli_query( $conn, "SELECT * FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = 1 AND topic_top = 1 ORDER BY topic_id DESC LIMIT $first, $last" );

		return $result_array;
	}
}


/**已知发帖人id查询发帖人名
 *
 * @param int $posterid
 *
 * @return array|$result|void
 */
function sql_poster_username( $posterid ) {
	global $conn;
	$result = mysqli_fetch_array( mysqli_query( $conn, "SELECT * FROM bbs0_users WHERE user_uid = '$posterid'" ) );

	return $result['user_username'];
}

/**插入一条主题
 *
 * @param int $forumid
 * @param string $title
 * @param int $poster
 * @param int $vote
 * @param string $content
 */
function sql_add_topic( $forumid, $title, $poster, $vote, $content ) {
	global $conn, $ip;
	$addtitle = addslashes( $title );
	$status   = ( $_SESSION['rank'] < 5 ) ? 2 : 1;
	$time     = time();
	$result   = mysqli_query( $conn, "INSERT INTO bbs0_topics (topic_forumid, topic_title, topic_poster, topic_createtime, topic_replies, topic_status, topic_vote, topic_type, topic_first_post_id, topic_last_post_id, topic_moved_id)VALUES('$forumid', '$addtitle', '$poster', $time, 0, '$status', '$vote', 1, 1, 1, 1)" );
	if ( ! $result ) {
		exit( 'fail1' . $forumid . $addtitle . $poster . $vote . $content );
	}
	$maxtopicid = mysqli_fetch_array( mysqli_query( $conn, "SELECT max(topic_id) FROM bbs0_topics" ) );
	$lastid     = $maxtopicid['max(topic_id)'];
	$result     = mysqli_query( $conn, "INSERT INTO bbs0_posts (post_topicid, post_type, post_content, post_authuid, post_createtime, post_uip, post_smilies, post_sig)VALUES('$lastid', 0, '$content', '$poster', $time, '$ip', 1, 1)" );
	if ( $result ) {
		//poster的文章数+1
		$sql    = "UPDATE bbs0_users SET user_posts=(user_posts+1) WHERE user_uid='$poster'";
		$update = mysqli_query( $conn, $sql );
		//板块文章数+1
		$sql2    = "UPDATE bbs0_forums SET form_topics=form_topics+1 WHERE forum_id='$forumid'";
		$update2 = mysqli_query( $conn, $sql2 );
		if ( $update ) {
			if ( $update2 ) {
				exit( 'success' );
			} else {
				exit( 'fail1.1.1' );
			}
		}
	} else {
		exit( 'fail2' . $ip );
	}
}

function sql_edit_topic( $title, $content, $vote, $tid, $type, $top, $fid, $selectForumVal ) {
	global $conn;
	$title   = addslashes( $title );
	$content = addslashes( $content );
	$time    = time();
	$sql1    = "UPDATE bbs0_topics SET topic_title = '$title', topic_vote = '$vote', topic_type = '$type', topic_top = '$top', topic_forumid = '$selectForumVal' WHERE bbs0_topics.topic_id = '$tid'";
	$sql2    = "UPDATE bbs0_posts SET post_content = '$content',post_edit_admin = 1, post_sdit_adimt_time = $time, post_edit_count = post_edit_count + 1  WHERE post_topicid = '$tid' AND post_type = 0";
	$result1 = mysqli_query( $conn, $sql1 );
	$result2 = mysqli_query( $conn, $sql2 );
	if ( $result1 ) {
		if ( $result2 ) {
			exit( 'success' );
		} else {
			exit( mysqli_error( $conn ) );
		}
	} else {
		exit( mysqli_error( $conn ) );
	}
}

/**已知topicid求topic所有内容
 *
 * @param int $topicid
 *
 * @return array|$result|void
 */
function sql_topicid_topics( $topicid ) {
	global $conn;
	$result = mysqli_fetch_array( mysqli_query( $conn, "SELECT * FROM bbs0_topics WHERE topic_id='$topicid'" ) );

	return $result;
}

/**已知topicid求posts所有内容
 *
 * @param int $topicid
 *
 * @return array|$result|void
 */
function sql_topicid_posts( $topicid, $type = 0 ) {
	global $conn;
	$result = mysqli_fetch_array( mysqli_query( $conn, "SELECT * FROM bbs0_posts WHERE post_topicid='$topicid' AND post_type='$type'" ) );

	return $result;
}

/**已知主题id求posts所有内容顺序
 *
 * @param int $topicid
 * @param int $type
 * @param int $start
 * @param int $last
 *
 * @return bool|$result|void
 */
function sql_topicid_posts_asc( $topicid, $type = 1, $start = 0, $last = 1 ) {
	global $conn;
	$result = mysqli_query( $conn, "SELECT * FROM bbs0_posts WHERE post_topicid='$topicid' AND post_type='$type' ORDER BY post_id ASC LIMIT $start, $last" );

	return $result;
}

/**
 * 已知postid求是否有楼中楼
 *
 * @param int $pid
 * @param int $type
 * @param int $start
 * @param int $limit
 *
 * @return bool|mysqli_result|void
 */
function sql_postid_lzl( $pid, $type = 2, $start = 0, $limit = 100 ) {
	global $conn;
	$result = mysqli_query( $conn, "SELECT * FROM bbs0_posts WHERE post_lzl = '$pid'  AND post_type = '$type' ORDER BY post_id ASC LIMIT $start, $limit" );

	return $result;
}

//
function sql_lzl_count( $pid, $type = 2 ) {
	global $conn;
	$query       = mysqli_query( $conn, "SELECT COUNT(1) FROM bbs0_posts WHERE post_lzl = '$pid' AND post_type = '$type'" );
	$fetch_array = mysqli_fetch_assoc( $query );

	return $fetch_array['COUNT(1)'];
}

/**回复主题
 *
 * @param int $topicid
 * @param int $type
 * @param string $content
 * @param int $authid
 * @param int $smilies
 * @param int $sig
 *
 * @return bool|mysqli_result|void
 */
function sql_reply_topic( $topicid, $type = 1, $content, $authid, $smilies = 1, $sig = 1 ) {
	global $conn, $ip, $time;
	$sql      = "INSERT INTO bbs0_posts (post_topicid, post_type, post_content, post_authuid, post_createtime, post_uip, post_smilies, post_sig)VALUES('$topicid', '$type', '$content', '$authid', $time, '$ip', $smilies, $sig)";
	$result   = mysqli_query( $conn, $sql );
	$result_1 = mysqli_query( $conn, "UPDATE bbs0_topics SET topic_replies = topic_replies + 1 WHERE topic_id = '$topicid'" );
	if ( $result ) {
		if ( $result_1 ) {
			exit( 'success' );
		} else {
			exit( mysqli_error( $conn ) );
		}
	} else {
		exit( mysqli_error( $conn ) );
	}
}

//楼中楼回复
function sql_add_lzl( $tid, $lzlcontent, $pid, $uid ) {
	global $conn, $ip;
	$content = addslashes( trim( $lzlcontent ) );
	$time    = time();
	$result  = mysqli_query( $conn, "INSERT INTO bbs0_posts (post_lzl, post_topicid, post_type, post_content, post_authuid, post_createtime, post_uip, post_smilies, post_sig)VALUES('$pid', '$tid', 2, '$content', '$uid', $time, '$ip', 1, 1)" );
	if ( $result ) {
		exit( "success" );
	} else {
		exit( mysqli_error( $conn ) );
	}
}

/**主题回复页数
 *
 * @param int $topicid
 * @param int $type
 *
 * @return bool|mysqli_result|void
 */
function sql_topic_page( $topicid ) {
	global $conn;
	$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_posts WHERE post_topicid='$topicid' AND post_type != 2" );

	return $count_result;
}

/**
 * 板块中主题页数
 *
 * @param int $forumid
 *
 * @return bool|mysqli_result|void
 */
function sql_forumid_page( $forumid ) {
	global $conn;
	$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid'" );

	return $count_result;
}

/**
 * 板块中正常主题页数
 *
 * @param int $forumid
 *
 * @return bool|mysqli_result|void
 */
function sql_forumid_nomal_type_page( $forumid, $type = 0 ) {
	global $conn;
	if ( $type != 0 ) {
		$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = 1 AND topic_type = '$type'" );

		return $count_result;
	} else {
		$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = 1" );

		return $count_result;
	}
}

function sql_forumid_nomal_fliter_page( $forumid, $fliter = 0 ) {
	global $conn;
	switch ( $fliter ) {
		case "0":
			$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid'" );

			return $count_result;
		case "1":
			$fliters      = 0;
			$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = $fliters" );

			return $count_result;
		case "2":
			$fliters      = 1;
			$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = $fliters" );

			return $count_result;
		case "3":
			$fliters      = 2;
			$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = $fliters" );

			return $count_result;
		default :
			$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid'" );

			return $count_result;
	}
}

function sql_count_forumid_toptopic( $forumid, $type = 0 ) {
	global $conn;
	if ( $type != 0 ) {
		$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = 1 AND topic_type = $type AND topic_top = 1" );

		return $count_result;
	} else {
		$count_result = mysqli_query( $conn, "SELECT COUNT(*) FROM bbs0_topics WHERE topic_forumid='$forumid' AND topic_status = 1 AND topic_top = 1" );

		return $count_result;
	}
}

//删除主题及其回复的所有内容
function sql_delTopic( $topicid, $forumid, $areaid ) {
	global $conn;
	//根据topic查出poster uid，要在被删除前查询
	$topics        = sql_topicid_topics( $topicid );
	$userid        = $topics['topic_poster'];
	$del_userposts = mysqli_query( $conn, "UPDATE bbs0_users SET user_posts=(user_posts-1) WHERE user_uid='$userid'" );

	$del_posts = mysqli_query( $conn, "DELETE FROM bbs0_posts WHERE post_topicid='$topicid'" );
	if ( ! $del_posts ) {
		exit( 'fail1' );
	}
	$del_topic          = mysqli_query( $conn, "DELETE FROM bbs0_topics WHERE topic_id='$topicid'" );
	$update_forum_topic = mysqli_query( $conn, "UPDATE bbs0_forums SET form_topics=form_topics-1 WHERE forum_id='$forumid'" );
	if ( $del_topic && $update_forum_topic ) {
		if ( $del_userposts ) {
			echo '<script>alert("删除成功");window.location.href="admin.php?type=area&id=' . $areaid . '&forumid=' . $forumid . '"</script>';
		} else {
			exit( 'fail1' . $topicid );
		}
		//header("Location:admin.php?type=area&id=$areaid&forumid=$forumid");
	} else {
		exit( 'fail2' . mysqli_error( $conn ) );
	}
}

//
function sql_topic_status( $tid ) {
	global $conn;
	$sql          = "SELECT topic_status FROM bbs0_topics WHERE topic_id = '$tid'";
	$result       = mysqli_fetch_array( mysqli_query( $conn, $sql ) );
	$topic_status = $result['topic_status'];
	switch ( $topic_status ) {
		case "0":
			return "隐藏 
            <select id='topic_status" . $tid . " onchange='ajaxpost_change_status($tid)'>
                <option value='0' selected='selected'>隐藏</option>
                <option value='1'>正常</option>
                <option value='2'>待审核</option>
            </select>
            ";
		case "1":
			return "正常 
            <select id='topic_status" . $tid . "' onchange='ajaxpost_change_status($tid)'>
                <option value='0'>隐藏</option>
                <option value='1' selected='selected'>正常</option>
                <option value='2'>待审核</option>
            </select>
            ";
		case "2":
			return "待审核 
            <select id='topic_status" . $tid . "' onchange='ajaxpost_change_status($tid)'>
                <option value='0'>隐藏</option>
                <option value='1'>正常</option>
                <option value='2' selected='selected'>待审核</option>
            </select>
            ";
		default :
			return "已损坏
            <select id='topic_status" . $tid . "' onchange='ajaxpost_change_status($tid)'>
                <option value='0'>隐藏</option>
                <option value='1'>正常</option>
                <option value='2' selected='selected'>待审核</option>
            </select>
            ";
	}
}

function sql_topic_status_user( $tid ) {
	global $conn;
	$sql          = "SELECT topic_status FROM bbs0_topics WHERE topic_id = '$tid'";
	$result       = mysqli_fetch_array( mysqli_query( $conn, $sql ) );
	$topic_status = $result['topic_status'];
	switch ( $topic_status ) {
		case "0":
			return "隐藏";
		case "1":
			return "正常";
		case "2":
			return "待审核";
		default :
			return "已损坏,请联系管理员或编辑";
	}
}

function sql_edit_status( $topicid, $statusval ) {
	global $conn;
	$sql    = "UPDATE bbs0_topics SET topic_status = '$statusval' WHERE topic_id = '$topicid'";
	$result = mysqli_query( $conn, $sql );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( "fail " . mysqli_error( $conn ) );
	}
}

//-----------反查

/**已知主题id求主题板块信息
 *
 * @param int $topicid
 *
 * @return array|null|void
 */
function sql_topicid_forums( $topicid ) {
	global $conn;
	$topics  = sql_topicid_topics( $topicid );
	$forumid = $topics['topic_forumid'];
	$sql     = "SELECT * FROM bbs0_forums WHERE forum_id='$forumid'";
	$result  = mysqli_fetch_array( mysqli_query( $conn, $sql ) );

	return $result;
}

/**已知板块id求论坛信息
 *
 * @param int $forumid
 *
 * @return array|null|void
 */
function sql_forumid_areas( $forumid ) {
	global $conn;
	$forums = sql_forumid_forums( $forumid );
	$areaid = $forums['forum_area_id'];
	$sql    = "SELECT * FROM bbs0_areas WHERE area_id='$areaid'";
	$result = mysqli_fetch_array( mysqli_query( $conn, $sql ) );

	return $result;
}


//搜索主题
function sql_search_topics( $content ) {
	global $conn;
	$addContent  = addslashes( trim( $content ) );
	$sql         = "SELECT * FROM bbs0_topics, bbs0_posts WHERE bbs0_topics.topic_id = bbs0_posts.post_topicid AND( bbs0_topics.topic_title LIKE '%$addContent%' OR bbs0_posts.post_content LIKE '%$addContent%' ) AND bbs0_posts.post_type = 0 ORDER BY `bbs0_posts`.`post_createtime` DESC";
	$result      = mysqli_query( $conn, $sql );
	$sql_count   = "SELECT COUNT(*) FROM bbs0_topics, bbs0_posts WHERE bbs0_topics.topic_id = bbs0_posts.post_topicid AND( bbs0_topics.topic_title LIKE '%$addContent%' OR bbs0_posts.post_content LIKE '%$addContent%' ) AND bbs0_posts.post_type = 0";
	$result_cont = mysqli_fetch_array( mysqli_query( $conn, $sql_count ) );

	return array( $result, $result_cont['COUNT(*)'] );
}

//查用户发布的主题,时间逆序
function sql_poster_topics( $uid ) {
	global $conn;
	$sql    = "SELECT * FROM bbs0_topics WHERE topic_poster='$uid' ORDER BY topic_createtime DESC ";
	$result = mysqli_query( $conn, $sql );

	return $result;
}

//查用户回复的帖子，时间逆序
function sql_authuid_posts( $uid ) {
	global $conn;
	$sql    = "SELECT * FROM bbs0_posts WHERE post_authuid='$uid' AND post_type=1 ORDER BY post_createtime DESC ";
	$result = mysqli_query( $conn, $sql );

	return $result;
}

//列出所有用户
function sql_list_users( $user_name, $first = 0, $num = 100 ) {
	global $conn;
	$sql       = "SELECT * FROM bbs0_users WHERE user_username LIKE '%$user_name%' OR user_uid LIKE '%$user_name%' ORDER BY user_uid ASC LIMIT $first, $num";
	$result    = mysqli_query( $conn, $sql );
	$count_sql = "SELECT COUNT(*) FROM bbs0_users WHERE user_username LIKE '%$user_name%' OR user_uid LIKE '%$user_name%' ORDER BY user_uid";
	$count     = mysqli_fetch_array( mysqli_query( $conn, $count_sql ) );

	return array( $result, $count['COUNT(*)'] );
}

//变更用户积分：
function sql_editCoins( $uid, $num ) {
	global $conn;
	$sql    = "UPDATE bbs0_users SET user_coins=(user_coins+$num) WHERE user_uid='$uid'";
	$result = mysqli_query( $conn, $sql );
	if ( $result ) {
		//exit('success');
	} else {
		exit( 'failcoin' );
	}
}

//变更用户经验：
function sql_addExp( $uid, $num ) {
	global $conn;
	$sql    = "UPDATE bbs0_users SET user_exp=(user_exp+$num) WHERE user_uid='$uid'";
	$result = mysqli_query( $conn, $sql );
	if ( $result ) {
		//exit('success');
		//并变更等级：
		$userall   = mysqli_fetch_array( mysqli_query( $conn, "SELECT * FROM bbs0_users WHERE user_uid='$uid'" ) );
		$userLevel = floor( $userall['user_exp'] / 10 );
		$result2   = mysqli_query( $conn, "UPDATE bbs0_users SET user_level=$userLevel WHERE user_uid='$uid'" );
		if ( ! $result2 ) {
			exit( 'faillevel' );
		}
	} else {
		exit( 'failcoin' );
	}
}

//变更用户名
function sql_change_username( $uid, $username ) {
	global $conn;
	$username_a = addslashes( $username );
	$sql        = "UPDATE bbs0_users SET user_username='$username_a' WHERE user_uid='$uid'";
	$result     = mysqli_query( $conn, $sql );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( "Error: " . mysqli_error( $conn ) );
	}
}

//变更用户密码
function sql_change_password( $uid, $get_password, $set_password ) {
	global $conn;
	$md5getpassword = md5( trim( $get_password ) );
	$sql_g          = "SELECT * FROM bbs0_users WHERE user_uid='$uid' AND user_password='$md5getpassword''";
	$result_g       = mysqli_fetch_array( mysqli_query( $conn, $sql_g ) );
	if ( $result_g != null ) {
		$md5setpassword = md5( trim( $set_password ) );
		$sql_s          = "UPDATE bbs0_users SET user_password='$md5setpassword' WHERE user_uid='$uid'";
		$result_s       = mysqli_query( $conn, $sql_s );
		if ( $result_s ) {
			exit( 'success' );
		} else {
			exit( '设置失败' . mysqli_error( $conn ) );
		}
	} else {
		exit( '原密码错误' );
	}
}

//变更用户信息
function sql_edit_users( $uid, $usersex, $userdate, $userwebsite, $ownspeak, $isignatur, $email ) {
	global $conn;
	$userwebsite_a = addslashes( $userwebsite );
	$ownspeak_a    = addslashes( $ownspeak );
	$sql           = "UPDATE bbs0_users SET user_sex=$usersex, user_birthday='$userdate', user_website='$userwebsite_a', user_ownspeak='$ownspeak_a', user_isignatur='$isignatur', user_email='$email' WHERE user_uid='$uid'";
	$resule        = mysqli_query( $conn, $sql );
	if ( $resule ) {
		exit( 'success' );
	} else {
		exit( 'fail ' . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
	}
}

//改变回帖状态（隐藏/显示）
function sql_edit_replyhide( $postid ) {
	global $conn;
	$sql               = "SELECT post_id, post_status FROM bbs0_posts WHERE post_id='$postid'";
	$postid_poststatus = mysqli_fetch_array( mysqli_query( $conn, $sql ) );
	if ( $postid_poststatus['post_status'] == 1 ) {
		$result = mysqli_query( $conn, "UPDATE bbs0_posts SET post_status=0 WHERE post_id = '$postid'" );
		if ( $result ) {
			exit( 'hidden' );
		} else {
			exit( '1' . mysqli_error( $conn ) );
		}
	} elseif ( $postid_poststatus['post_status'] == 0 ) {
		$result = mysqli_query( $conn, "UPDATE bbs0_posts SET post_status=1 WHERE post_id = '$postid'" );
		if ( $result ) {
			exit( 'showed' );
		} else {
			exit( '2' . mysqli_error( $conn ) );
		}
	} else {
		exit( 'nothing to do' );
	}

}

function sql_delUser( $uid ) {
	global $conn;
	$sql1    = "DELETE FROM bbs0_posts WHERE post_authuid='$uid'";
	$result1 = mysqli_query( $conn, $sql1 );
	if ( $result1 ) {
		$sql2    = "DELETE FROM bbs0_topics WHERE topic_poster='$uid'";
		$result2 = mysqli_query( $conn, $sql2 );
		if ( $result2 ) {
			$sql3    = "DELETE FROM bbs0_users WHERE user_uid='$uid'";
			$result3 = mysqli_query( $conn, $sql3 );
			if ( $result3 ) {
				exit( 'success' );
			} else {
				exit( 'fail3 ' . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
			}
		} else {
			exit( 'fail2 ' . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
		}
	} else {
		exit( 'fail1 ' . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
	}

}

//修改用户组
function sql_editRank( $uid, $rank ) {
	global $conn;
	$sql    = "UPDATE bbs0_users SET user_rank='$rank' WHERE user_uid='$uid'";
	$result = mysqli_query( $conn, $sql );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail ' . mysqli_errno( $conn ) . " " . mysqli_error( $conn ) );
	}
}

//查询拥有的徽章
function sql_search_medals( $uid ) {
	global $conn;
	$sql    = "SELECT medal_id FROM bbs0_user_medals WHERE medal_uid='$uid'";
	$result = mysqli_query( $conn, $sql );

	return $result;
}

//添加及修改徽章
function sql_add_medal( $medal_name, $medal_url, $medal_sale ) {
	global $conn;
	$medal_name = addslashes( $medal_name );
	$medal_url  = addslashes( $medal_url );
	$medal_sale = addslashes( $medal_sale );
	$sql        = "INSERT INTO bbs0_medals (medal_name, medal_url, medal_sale)VALUES ('$medal_name', '$medal_url', $medal_sale)";
	$result     = mysqli_query( $conn, $sql );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail ' . mysqli_errno( $conn ) . mysqli_error( $conn ) );
	}
}

//所有徽章list
function sql_list_medal() {
	global $conn;
	$sql    = "SELECT * FROM bbs0_medals ORDER BY medal_id ASC";
	$result = mysqli_query( $conn, $sql );

	return $result;
}


//兑换徽章
function sql_buy_medal( $uid, $medalid ) {
	global $conn;
	//最先搜索是否已经拥有：
	$sql4    = "SELECT COUNT(1) FROM bbs0_user_medals WHERE medal_uid='$uid' AND medal_id='$medalid'";
	$result4 = mysqli_fetch_array( mysqli_query( $conn, $sql4 ) );
	if ( $result4['COUNT(1)'] != 0 ) {
		exit( 'had' );
	}

	//先搜索用户积分
	$sql1    = "SELECT user_coins FROM bbs0_users WHERE user_uid='$uid'";
	$result1 = mysqli_fetch_array( mysqli_query( $conn, $sql1 ) );
	//搜索徽章所需要的积分：
	$sql2    = "SELECT medal_sale FROM bbs0_medals WHERE medal_id='$medalid'";
	$result2 = mysqli_fetch_array( mysqli_query( $conn, $sql2 ) );
	//对比
	if ( $result1 < $result2 ) {
		exit( 'unenough' );
	}

	$sql = "INSERT INTO bbs0_user_medals (medal_uid, medal_id)VALUES($uid, $medalid)";
	//减去积分:
	$score  = $result2['medal_sale'];
	$sql3   = "UPDATE bbs0_users SET user_coins=user_coins-$score WHERE user_uid='$uid'";
	$result = mysqli_query( $conn, $sql );
	if ( $result ) {
		$result3 = mysqli_query( $conn, $sql3 );
		if ( $result3 ) {
			exit( 'success' );
		} else {
			exit( 'fail2 ' . mysqli_errno( $conn ) . mysqli_error( $conn ) );
		}
	} else {
		exit( 'fail1 ' . mysqli_errno( $conn ) . mysqli_error( $conn ) );
	}
}

//已知徽章id求徽章名和url
function sql_medalid_medals( $mid ) {
	global $conn;
	$sql    = "SELECT medal_url, medal_name FROM bbs0_medals WHERE medal_id='$mid'";
	$result = mysqli_fetch_array( mysqli_query( $conn, $sql ) );

	return $result;
}


//删除用户徽章
function sql_delUserMedal( $uid, $mid ) {
	global $conn;
	$sql    = "DELETE FROM bbs0_user_medals WHERE medal_uid = '$uid' AND medal_id = '$mid'";
	$result = mysqli_query( $conn, $sql );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( 'fail1 ' . mysqli_errno( $conn ) . mysqli_error( $conn ) );
	}
}

//添加回帖通知
function sql_addNotify( $topicid, $uid ) {
	global $conn;
	//查出帖子发表人
	$sql          = "SELECT topic_poster FROM bbs0_topics WHERE topic_id = '$topicid'";
	$poster_array = mysqli_fetch_array( mysqli_query( $conn, $sql ) );
	$posterid     = $poster_array['topic_poster'];
	if ( $posterid != $uid ) {
		$sql_addnotify = "UPDATE bbs0_users SET user_notify = 1 WHERE user_uid = '$posterid'";
		$result        = mysqli_query( $conn, $sql_addnotify );
		if ( ! $result ) {
			exit( "failaddnotify " . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
		}
	}
}

//删除回帖标记
function sql_delNotify( $uid ) {
	global $conn;
	$sql    = "UPDATE bbs0_users SET user_notify = 0 WHERE user_uid = '$uid'";
	$result = mysqli_query( $conn, $sql );
	if ( ! $result ) {
		exit( "faildelnotify " . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
	}
}

//增加积分
function sql_addCoins( $uid, $addCoins ) {
	global $conn;
	$sql    = "UPDATE bbs0_users SET user_coins=user_coins+$addCoins WHERE user_uid='$uid'";
	$result = mysqli_query( $conn, $sql );
	if ( ! $result ) {
		exit( "failaddcoins " . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
	} else {
		exit( 'success' );
	}
}

function sql_coin_name() {
	global $conn;
	$sql          = "SELECT set_rankname FROM bbs0_ranksetting WHERE set_ranknumber=11";
	$result_array = mysqli_fetch_array( mysqli_query( $conn, $sql ) );

	return $result_array['set_rankname'];
}

function sql_editCoinName( $scoreName ) {
	global $conn;
	$scoreName = addslashes( $scoreName );
	$sql       = "UPDATE bbs0_ranksetting SET set_rankname = '$scoreName' WHERE set_ranknumber=11 ";
	$result    = mysqli_query( $conn, $sql );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( "faileditCoinName " . mysqli_errno( $conn ) . ' ' . mysqli_error( $conn ) );
	}
}


function sql_add_key( $key, $change ) {
	global $conn;
	$key    = addslashes( trim( $key ) );
	$change = addslashes( trim( $change ) );
	$sql    = "INSERT INTO bbs0_key_del (key_name, key_change)VALUES('$key', '$change')";
	$result = mysqli_query( $conn, $sql );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( mysqli_error( $conn ) );
	}

}

function sql_key_query() {
	global $conn;
	$sql    = "SELECT id, key_name, key_change FROM bbs0_key_del ORDER BY id DESC ";
	$result = mysqli_query( $conn, $sql );

	return $result;
}

function sql_del_key( $kid ) {
	global $conn;
	$result = mysqli_query( $conn, "DELETE FROM bbs0_key_del WHERE id='$kid'" );
	if ( $result ) {
		exit( 'success' );
	} else {
		exit( mysqli_error( $conn ) );
	}
}

function sql_change_bg( $uid, $color ) {
	global $conn;
	if ( $uid != 0 ) {
		$sql    = "UPDATE bbs0_users SET user_theme = '$color' WHERE user_uid = '$uid'";
		$result = mysqli_query( $conn, $sql );
		if ( $result ) {
			$_SESSION['color'] = $color;
			exit( 'success' );
		} else {
			exit( mysqli_error( $conn ) );
		}
	} else {
		$_SESSION['color'] = $color;
		exit( 'success' );
	}
}

function sql_edit_replydel( $postid ) {
	global $conn;
	$topicid  = sql_postid_topicid( $postid );
	$result_1 = mysqli_query( $conn, "UPDATE bbs0_topics SET topic_replies = topic_replies - 1 WHERE topic_id = '$topicid'" );
	$result   = mysqli_query( $conn, "DELETE FROM bbs0_posts WHERE post_id = '$postid'" );
	$result_2  = mysqli_query( $conn, "DELETE FROM bbs0_posts WHERE post_lzl = '$postid'" );
	if ( $result ) {
		if ( $result_1 ) {
			if ( $result_2 ) {
				exit( 'success' );
			} else {
				exit( mysqli_error( $conn ) );
			}
		} else {
			exit( mysqli_error( $conn ) );
		}
	} else {
		exit( mysqli_error( $conn ) );
	}
}

function sql_postid_topicid( $postid ) {
	global $conn;
	$result         = mysqli_fetch_array( mysqli_query( $conn, "SELECT post_topicid FROM bbs0_posts WHERE post_id = '$postid'" ) );
	$result_topicid = $result['post_topicid'];
	if ( $result ) {
		return $result_topicid;
	} else {
		exit( mysqli_error( $conn ) );
	}
}

function sql_addLike( $topicid ) {
	global $conn;
	$uid        = $_SESSION['uid'];
	$search     = mysqli_fetch_array( mysqli_query( $conn, "SELECT topic_like FROM bbs0_topics WHERE topic_id = '$topicid'" ) );
	$searchText = $search['topic_like'];
	$result1    = mysqli_fetch_array( mysqli_query( $conn, "SELECT topic_like FROM bbs0_topics WHERE topic_id = '$topicid'" ) );
	$count      = count( explode( ',', $result1['topic_like'] ) ) - 1;
	foreach ( explode( ',', $searchText ) AS $huid ) {
		if ( $uid == $huid ) {
			echo $count . "|您已经点赞过了。";
			exit();
		}
	}
	$result  = mysqli_query( $conn, "UPDATE bbs0_topics SET topic_like = CONCAT(topic_like,',',$uid) WHERE topic_id = '$topicid'" );
	$result1 = mysqli_fetch_array( mysqli_query( $conn, "SELECT topic_like FROM bbs0_topics WHERE topic_id = '$topicid'" ) );
	$counts  = count( explode( ',', $result1['topic_like'] ) ) - 1;
	echo $counts . "|点赞成功！";
}

//根据fid查待审核数
function sql_count_forumid_waittopic( $fid ) {
	global $conn;
	$result_a = mysqli_fetch_array( mysqli_query( $conn, "SELECT COUNT(1) FROM bbs0_topics WHERE topic_forumid = '$fid' AND topic_status = 2" ) );
	$result   = $result_a['COUNT(1)'];

	return $result;
}

//最后一贴时间
function sql_time_final_ftid( $tid ) {
	global $conn;
	$result = mysqli_fetch_array( mysqli_query( $conn, "SELECT post_createtime FROM bbs0_posts WHERE post_topicid = '$tid' ORDER BY post_createtime DESC LIMIT 1" ) );

	return $result['post_createtime'];
}

//帖子状态
function sql_topicid_type( $tid ) {
	global $conn;
	$result      = mysqli_fetch_array( mysqli_query( $conn, "SELECT topic_type FROM bbs0_topics WHERE topic_id = '$tid'" ) );
	$result_type = $result['topic_type'];

	return $result_type;
}

function sql_selectFrom( $areaid ) {
	global $conn;
	$result     = mysqli_query( $conn, "SELECT forum_name, forum_id FROM bbs0_forums WHERE forum_area_id = '$areaid'" );
	$result_out = null;
	while ( ( $result_array = mysqli_fetch_array( $result ) ) != false ) {
		$result_out .= "<option value='" . $result_array['forum_id'] . "'>" . $result_array['forum_name'] . "</option>";
	}
	if ( $result ) {
		return htmlspecialchars_decode( $result_out );
	} else {
		return mysqli_error( $conn );
	}
}

